import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

// updates a reservation on the database

public class SqlTest4
{
	
	  public static void main(String args[]) throws SQLException {
	
		  Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/bedandbreakfast",
					  "root", "");	
		  Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
		                                       ResultSet.CONCUR_UPDATABLE);
		  ResultSet uprs = stmt.executeQuery("SELECT P_id, guestName, roomNumber, totalNumberOfGuests, checkIn, checkOut FROM reservation WHERE P_id = 8");
				  
		  System.out.println("Reservation that was retrieved: ");
		  
		  while (uprs.next()) {
		  	int P_id = uprs.getInt("P_id");
		  	String guestName = uprs.getString("guestName");
	        int roomNumber = uprs.getInt("roomNumber");
	        int totalNumberOfGuests = uprs.getInt("totalNumberOfGuests");
	        String checkIn = uprs.getString("checkIn");
	        String checkOut = uprs.getString("checkOut");
	        System.out.println("Reservation Number: " + P_id + "    Guest Name: " + guestName + "    Room Number: " + roomNumber + "    Total Number of Guests: " + totalNumberOfGuests + "    Check In: " + checkIn + "    Check Out: " + checkOut);
		 
		  		  //uprs.next();
				  uprs.updateString("GuestName", "Darth Vadar");
				  uprs.updateInt("roomNumber", 104);
				  uprs.updateInt("totalNumberOfGuests", 1);
				  uprs.updateString("checkIn", "02-01-2010");
				  uprs.updateString("checkOut", "02-28-2010");
				  uprs.updateRow();

		System.out.println("");		
		System.out.println("Reservation altered to: ");
				  
				int P_id2 = uprs.getInt("P_id");
		        String guestName2 = uprs.getString("guestName");
		        int roomNumber2 = uprs.getInt("roomNumber");
		        int totalNumberOfGuests2 = uprs.getInt("totalNumberOfGuests");
		        String checkIn2 = uprs.getString("checkIn");
		        String checkOut2 = uprs.getString("checkOut");
		        System.out.println("Reservation Number: " + P_id2 + "    Guest Name: " + guestName2 + "    Room Number: " + roomNumber2 + "    Total Number of Guests: " + totalNumberOfGuests2 + "    Check In: " + checkIn2 + "    Check Out: " + checkOut2);
		      
		}

	  }

}
